# -*- coding: utf-8 -*-


from program_top.utilities.data_container_operations import nested_dict_apply
from program_top.utilities.string_and_unicode import to_string_if_unicode
from os import linesep

'''
from sqlalchemy import create_engine,MetaData,inspect
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
'''

import pyodbc

sqlalchemy_dialect={
	'microsoft':'mssql+pymssql'
}

def quick_mapper(table):
	Base = declarative_base()
	class GenericMapper(Base):
		__table__ = table
		pass
	return GenericMapper

def row_line_to_text_content(each_row):
	'''only_for_SQL_on_windows'''
	row_content=str(each_row)
	puzzle=each_row[0]
	string_puzzle=puzzle.encode("utf-8")
	return string_puzzle



class sql_session(object):
	def __init__(self,db_config):
		self.__db_config=db_config
		self.make_pyodbc_session()
		pass
	
	def __rows_to_text(self,rows):
		a=[row_line_to_text_content(each_row) for each_row in rows]
		result="".join(a)
		return result
	
	def extract_procedure_code(self,procedure_name):
		final_text=None

		try:
			operation_string="sys.sp_helptext %s;"%(procedure_name)
			self.odbc_cursor.execute(operation_string)
			result=self.odbc_cursor.fetchall()
			final_text=self.__rows_to_text(result)
			
		except Exception, current_error:
			print current_error.__repr__()
			pass
		
		return final_text
	
	def transmit_records(self,destination_instance,destination_table_name,source_table_name):
		db_name=self.__db_config['db_name']
		table_absolute_name="%s.dbo.%s"%(db_name, source_table_name)
		operation_string="SELECT * FROM %s"%(table_absolute_name)
		try:
			self.odbc_cursor.execute(operation_string)
			batch_size=self.__db_config['record_batch_size']
			while 1:
				loop=self.odbc_cursor.fetchmany(batch_size)
				result_dict_list=[self.__pyodbc_row_to_dict(each_row) for each_row in loop]
				destination_instance.write_records(destination_table_name,result_dict_list)
				#print '写入完成'
				if loop.__len__()<batch_size:
					break
				
				pass
		except Exception, current_error:
			print current_error.__repr__()
			pass
			
		pass
	
	def __generate_exe_many_insert_value_tuple(self,keys_sequence,record):
		value_list_according_to_key_sequence=[]
		for each_key in keys_sequence:
			value=record[each_key]
			if not isinstance(value, str):#不是字符串的情况
				value_to_write=str(value)
			else:#是字符串的情况
				if value.__contains__("'"):
					value=value.replace("'", "''")#转义单引号
					pass
				value_to_write="%s"%value
				pass
			
			value_list_according_to_key_sequence.append(value_to_write)
			pass
		
		return tuple(value_list_according_to_key_sequence)
	
	def write_records(self,table_name,records_to_write):
		db_name=self.__db_config['db_name']
		table_absolute_name="%s.dbo.%s"%(db_name,table_name)
		key_title_sequence=records_to_write[0].keys()
		key_title_string=', '.join(key_title_sequence)
		value_blank_string=', '.join(key_title_sequence.__len__()*'?')
		operation_string="INSERT INTO %s (%s) VALUES (%s)"%(table_absolute_name,key_title_string,value_blank_string)
		many_parameters=[self.__generate_exe_many_insert_value_tuple(key_title_sequence,each_record) for each_record in records_to_write]
		try:
			self.odbc_cursor.executemany(operation_string,many_parameters)
			self.odbc_cursor.commit()
		except Exception, current_error:
			print current_error.__repr__()
			pass
		'''
		for each_record in records_to_write:
			try:
				self.__insert_single_record(table_absolute_name,each_record)
			except Exception, current_error:
				print current_error.__repr__()
				pass
			pass
			'''
		pass
	
	def read_all_records(self,table_name):
		db_name=self.__db_config['db_name']
		operation_string="SELECT * FROM %s.dbo.%s"%(db_name,table_name)
		return self.__select_and_fetch(operation_string)
	
	def __insert_single_record(self,table_absolute_name,record):
		operation_string="INSERT INTO %s ("%(table_absolute_name)
		key_string=""
		value_string="VALUES ("
		
		for each_key in record.keys():
			key_string+=' %s,'%each_key
			value= record[each_key]
			
			if not  isinstance(value,str):#不是字符串的情况
				value_to_write=str(value)
			else:#是字符串的情况
				if value.__contains__("'"):
					value=value.replace("'","''")#转义单引号
					pass
				value_to_write="'%s'"%value
				pass
			
			value_string+=' %s,'%value_to_write
			pass
		
		new_key_string=key_string[:-1]
		new_key_string+=') '
		new_value_string=value_string[:-1]
		new_value_string+=');'
		
		new_operation_string=''.join([operation_string,new_key_string,new_value_string])
		try:
			self.odbc_cursor.execute(new_operation_string)
			self.odbc_cursor.commit()
		except Exception, current_execption:
			print current_execption.__repr__()
			pass
		pass
	
	def __create_table_with_given_columns(self,table_name,columns_info):
		'''根据列信息创建表'''
		
		if self.contain_table(table_name):
			return
		
		operation_string="CREATE TABLE %s.dbo.%s ("%(self.__db_config['db_name'],table_name)
		
		for each_column in columns_info:
			column_name=each_column['COLUMN_NAME']
			value_type=each_column['DATA_TYPE']
			type_string=value_type
			if value_type=='varchar' or value_type=='char':
				max_size=max(each_column['CHARACTER_MAXIMUM_LENGTH'], each_column['CHARACTER_OCTET_LENGTH'])
				if max_size<2000:
					max_size+=3
				
				type_string="%s(%d)"%(value_type, max_size)
				
			
			if value_type=='decimal':
				type_string="%s(%d,%d)"%(value_type,each_column['NUMERIC_PRECISION'],each_column['NUMERIC_SCALE'])
				
			column_line="%s %s"%(column_name,type_string)
			
			
			if each_column['IS_NULLABLE']=='NO':
				column_line+=' NOT NULL'
			
			if each_column['COLUMN_DEFAULT']:#函数型默认值
				#print each_column['COLUMN_DEFAULT']
				default_func=each_column['COLUMN_DEFAULT']
				if default_func.startswith('(') and default_func.endswith(')'):
					default_func=default_func[1:-1].upper()
				else:
					print "请处理本列的非函数型默认值"
					pass
				
				column_line+=" DEFAULT %s"%default_func
				
				pass
			
			column_line+=','
			operation_string+=column_line
			pass
		
		new_string=operation_string[:-1]
		
		new_string+=');'
		try:
			self.odbc_cursor.execute(new_string)
			self.odbc_cursor.commit()
		except Exception, current_error:
			error_msg=current_error.__repr__()
			print error_msg
			pass
		
		pass
	
	def __add_constraints(self,table_name,constraints):
		#以及未来的值约束，和唯一约束
		
		db_name=self.__db_config['db_name']
		pk_constraints_list=[]#主键约束列表
		
		for each in constraints:
			if each['CONSTRAINT_NAME'].startswith('PK'):
				pk_constraints_list.append(each)
			else:
				print '非主键约束的情况，要完善'
				print table_name
				pass
				
		pk_constraints_name=pk_constraints_list[0]['CONSTRAINT_NAME']#约束名
		column_names=[each_constraint['COLUMN_NAME'] for each_constraint in pk_constraints_list]
		operation_string="ALTER TABLE %s.dbo.%s ADD CONSTRAINT %s PRIMARY KEY (%s)"%(db_name, table_name,pk_constraints_name,','.join(column_names))
		
		self.odbc_cursor.execute(operation_string)
		self.odbc_cursor.commit()
		
		pass
	
	def create_table_with_meta(self,table_name,table_meta):
		'''根据表信息创建表'''
		self.__create_table_with_given_columns(table_name,table_meta['all_columns_info'])
		self.__add_constraints(table_name,table_meta['column_constraints'])
		pass
		
	def read_table_meta(self,table_name):
		'''读取表的各列信息，包括字段类别，唯一性束，对应的主键，值约束，返回做个字典'''
		all_columns_info=self.__read_columns(table_name)
		column_constraints=self.__read_constraints(table_name)
		
		return {'all_columns_info': all_columns_info,'column_constraints':column_constraints}
	
	def __loop_query(self,operation_string):
		result=[]
		
		#print operation_string
		result_handle=self.odbc_cursor.execute(operation_string)
		#print '完成执行，接下来fetch数据'
		
		buffer_count=self.__db_config['record_batch_size']
		
		try:
			while 1:
				loop=result_handle.fetchmany(buffer_count)
				result.extend(loop)
				#print result.__len__(),loop.__len__()
				if loop.__len__()<buffer_count:
					break
				pass
		except Exception, current_error:
			print current_error.__repr__()
			pass
		
		return result
	
	def __select_and_fetch(self,operation_string):
		'''按照字符串select并返回结果'''
		result=self.__loop_query(operation_string)
		result_dict_list=[self.__pyodbc_row_to_dict(each_row) for each_row in result]
		return result_dict_list
	
	def __read_constraints(self,table_name):
		'''读取主键、唯一性、值域这三类约束'''
		operation_string="SELECT * FROM [%s].[INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] where TABLE_NAME='%s' order by TABLE_NAME"%(self.__db_config['db_name'], table_name)
		constraints=self.__select_and_fetch(operation_string)
		return constraints
	
	def __read_columns(self,table_name):
		'''读取各列的列名和对应的值类型'''
		
		operation_string="SELECT * FROM [%s].[INFORMATION_SCHEMA].[COLUMNS] where TABLE_NAME='%s' order by TABLE_NAME"%(self.__db_config['db_name'],table_name)
		all_columns=self.__select_and_fetch(operation_string)
		
		return all_columns
		
	def contain_table(self,table_name):
		for each_table in self.tables:
			if each_table['TABLE_NAME']==table_name:
				return table_name
			pass
		return None
	
	def __pyodbc_row_to_dict(self,row):
		columns=[column[0] for column in row.cursor_description]
		result=dict(zip(columns, row))
		return nested_dict_apply(result,to_string_if_unicode)
		
	def make_pyodbc_session(self):
		server_address=self.__db_config['host']
		port=self.__db_config['port']
		instance_name=self.__db_config['db_instance_name']
		user_name=self.__db_config['login_name']
		password=self.__db_config['password']
		server_address_with_port="%s,%d"%(server_address,port)
		
		try:
			self.connection=pyodbc.connect(driver='SQL Server Native Client 10.0', server=server_address_with_port, database=instance_name, uid=user_name, pwd=password,language='us_english')#创建odbc连接
			print '连接创建完成'
		except Exception, current_error:
			print current_error.__repr__()
			pass
			
		self.odbc_cursor=self.connection.cursor()#创建游标
		self.procedures=self.__list_all_procedures()
		self.tables=self.list_all_tables()
		
		pass
	
	def __list_all_procedures(self):
		" select * from DatabaseName.information_schema.routines where routine_type='PROCEDURE'"
		db_name=self.__db_config['db_name']
		operation_string="SELECT * FROM %s.INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE'"%db_name
		raw_result=self.__select_and_fetch(operation_string)
		target_dict={each_procedure['ROUTINE_NAME']:each_procedure for each_procedure in raw_result}
		return target_dict

	def make_alchemy_session(self, connection_string):
		
		engine=create_engine(connection_string, echo=False, convert_unicode=False,encoding='utf-8')
		Session=sessionmaker(bind=engine)
		self.session=Session()
		self.engine=engine
		
		convention={"ix": 'ix_%(column_0_label)s', "uq": "uq_%(table_name)s_%(column_0_name)s"}
		
		self.metadata=MetaData(bind=self.engine,naming_convention=convention)
		pass
	
	def list_all_tables(self):
		db_name=self.__db_config['db_name']
		operation_string="SELECT * FROM %s.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'"%(db_name)
		return self.__select_and_fetch(operation_string)
	pass



def pyodbc_row_record2dict(pyodbc_record):
	'''将pyodbc的一行查询结果变成字典'''
	columns=[column[0] for column in pyodbc_record.cursor_description]
	a_result=dict(zip(columns, pyodbc_record))
	return a_result




class ms_sql_adapter(object):
	def __init__(self,db_config):
		super(ms_sql_adapter, self).__init__()
		connection_driver_info='{SQL Server}'
		server_info=db_config['host']+','+str(db_config['port'])+'\\'+db_config['db_instance_name']
		db_info=db_config['db_name']
		uid=db_config['login_name']
		pwd=db_config['password']
		#dialect+driver://username:password@host:port/database
		
		sqlalchemy_connection_string='mssql+pyodbc://%s:%s@%s:%d/%s'%(uid,pwd,db_config['host'],db_config['port'],db_config['db_instance_name'])
		
		self.__current_engine=create_engine(sqlalchemy_connection_string)
		self.metadata=MetaData(self.__current_engine)
		
		
		
		pass
	
	def scan_all_tables(self):
		
		
		
		all_tables=self.mssql_cur.tables()
		
		table_list=[]
		
		for rows in all_tables:
			if rows.table_type=='TABLE':
				table_list.append(rows.table_name)
			else:
				#print rows
				pass
			pass
		all_tables_result=nested_dict_apply(target_data_for_json=table_list, operation_function=to_string_if_unicode)
		return all_tables_result
	pass